﻿using System;
using System.Collections.Generic;
using System.Data;
using DbUp.Engine;
using DbUp.Engine.Output;

namespace DbUp.PgSql
{
   /// <summary>
   /// An implementation of the <see cref="IJournal"/> interface which tracks version numbers for a
   /// PostgreSQL database using a table called dbo.SchemaVersions.
   /// </summary>
   public sealed class PgSqlTableJournal : IJournal
   {
      private readonly Func<IDbConnection> connectionFactory;
      private readonly string tableName;
      private readonly string schema;
      private readonly string schemaTableName;
      private readonly IUpgradeLog log;

      /// <summary>
      /// Initializes a new instance of the <see cref="PgSqlTableJournal"/> class.
      /// </summary>
      /// <param name="connectionFactory">The connection factory.</param>
      /// <param name="schema">The schema that contains the table.</param>
      /// <param name="table">The table name.</param>
      /// <param name="logger">The log.</param>
      /// <example>
      /// var journal = new PgSqlTableJournal("server=server;database=database;uid=postgres;pwd=secret", "public", "MyVersionTable");
      ///   </example>
      public PgSqlTableJournal( Func<IDbConnection> connectionFactory, string schema, string table, IUpgradeLog logger )
      {
         this.connectionFactory = connectionFactory;
         tableName = table;
         this.schema = schema ?? "public";
         schemaTableName = string.Format( "\"{0}\"", table );
         if( !string.IsNullOrEmpty( schema ))
         {
            schemaTableName = string.Format( "\"{0}\".", schema ) + schemaTableName;
         }
         log = logger;
      }

      /// <summary>
      /// Recalls the version number of the database.
      /// </summary>
      /// <returns>All executed scripts.</returns>
      public string[] GetExecutedScripts()
      {
         log.WriteInformation( "Fetching list of already executed scripts." );
         if ( !DoesTableExist() )
         {
            log.WriteInformation( string.Format( "The {0} table could not be found. The database is assumed to be at version 0.", schemaTableName ) );
            return new string[0];
         }

         var scripts = new List<string>();
         using ( var connection = connectionFactory() )
         using ( var command = connection.CreateCommand() )
         {
            command.CommandText = string.Format( "select \"ScriptName\" from {0} order by \"ScriptName\"", schemaTableName );
            command.CommandType = CommandType.Text;
            connection.Open();

            using ( var reader = command.ExecuteReader() )
            {
               while ( reader.Read() )
                  scripts.Add( ( string )reader[0] );
            }
         }
         return scripts.ToArray();
      }

      /// <summary>
      /// Records a database upgrade for a database specified in a given connection string.
      /// </summary>
      /// <param name="script">The script.</param>
      public void StoreExecutedScript( SqlScript script )
      {
         var exists = DoesTableExist();
         if ( !exists )
         {
            log.WriteInformation( string.Format( "Creating the {0} table", schemaTableName ) );

            using ( var connection = connectionFactory() )
            using ( var command = connection.CreateCommand() )
            {
               command.CommandText = string.Format(
@"create table {0} (
        id serial constraint PK_SchemaVersions_Id primary key ,
        scriptname varchar(255) not null,
        applied date not null
)", schemaTableName );

               command.CommandType = CommandType.Text;
               connection.Open();

               command.ExecuteNonQuery();
            }

            log.WriteInformation( string.Format( "The {0} table has been created", schemaTableName ) );
         }


         using ( var connection = connectionFactory() )
         using ( var command = connection.CreateCommand() )
         {
            command.CommandText = string.Format( "insert into {0} (scriptname, applied) values (:scriptname, :applied)", schemaTableName );

            var param1 = command.CreateParameter();
            param1.ParameterName = "scriptname";
            param1.Value = script.Name;
            command.Parameters.Add( param1 );

            var param2 = command.CreateParameter();
            param2.ParameterName = "applied";
            param2.Value = DateTime.UtcNow;
            command.Parameters.Add( param2 );

            command.CommandType = CommandType.Text;
            connection.Open();

            command.ExecuteNonQuery();
         }
      }

      private bool DoesTableExist()
      {
         using ( var connection = connectionFactory() )
         {
            using ( var command = connection.CreateCommand() )
            {
               command.CommandText = string.Format( "select count(*) from pg_tables where schemaname='{0}' and tablename='{1}';", schema, tableName );
               command.CommandType = CommandType.Text;
               connection.Open();
               return Convert.ToInt64( command.ExecuteScalar() ) != 0;
            }
         }
      }
   }
}
